教學來源:
Normalization - 1NF, 2NF, 3NF and 4NF
Java JDBC Tutorial - Part 0: Overview

Mysql conectors下載:
https://www.mysql.com/products/connector/

下載完後，會有個壓縮檔，資料夾是mysql-connector-java-8.0.17，檔案有:

新版本的mysql-connector-java-8.0.17.jar檔案跟之前版本的mysql-connector-java-5.1.48.jar內容不太一樣，所以舊程式會有一些錯誤。這邊先練習mysql-connector-java-5.1.48

回去下載mysql-connector-java-5.1.48:

裡面的mysql-connector-java-5.1.48.jar，又是一個壓縮檔，程式裡看到的com.mysql.jdbc.Driver，就在這裡，所以java要連接mysql需要的檔案，就是mysql-connector-java-5.1.48.jar這個壓縮檔。

那就來開始寫程式連接mysql

1 開啟IntelliJ IDEA
2 open module setting

3 把mysql-connector-java-5.1.48.jar的路徑貼上--> Apply-->OK

4 開始寫程式，練習三個:
一 一般方法

import java.sql.*;

public class jdbc1 {
 public static void main(String[] args) {
  try {
   Class.forName("com.mysql.jdbc.Driver");
   System.out.println("Driver loaded!");
  } catch (ClassNotFoundException e) {
   System.out.println("找不到驅動程式類別");
   e.printStackTrace();
  }

  try {
   Connection connection = getConnection();
   Statement statement = connection.createStatement();
   // 查詢city表
   try (ResultSet resultSet = statement.executeQuery("SELECT * FROM city")) {
    while (resultSet.next()) {
     System.out.println(resultSet.getString("Name") + "    " + resultSet.getString(
      "CountryCode") + "   " + resultSet.getString("District"));
    }
   }
  } catch (SQLException e) {
   e.printStackTrace();
  }
 }

 //設定檔
 private static Connection getConnection() throws SQLException {
  String serverName = "localhost";
  String database = "world";
  String url = "jdbc:mysql://" + serverName + "/" + database;
  // 帳號和密碼
  String user = "root";
  String password = "root";
  return DriverManager.getConnection(url, user, password);
 }
}

二 rowset(不太了解rowset是什麼，之後再說)

import javax.sql.RowSet;
import javax.sql.rowset.RowSetFactory;
import javax.sql.rowset.RowSetProvider;
import java.sql.*;


public class jdbc2 {
 public static void main(String[] args) throws Exception {
  try {
   Class.forName("com.mysql.jdbc.Driver");
   System.out.println("Driver loaded!");
  } catch (ClassNotFoundException e) {
   System.out.println("找不到驅動程式類別");
   e.printStackTrace();
  }

  RowSetFactory factory = RowSetProvider.newFactory();
  try (RowSet rowSet = factory.createJdbcRowSet()) {
   getConnection1(rowSet);
   getConnection1(rowSet).setCommand("SELECT * FROM city");
   getConnection1(rowSet).execute();
   while (rowSet.next()) {
    System.out.print(rowSet.getString("Name") + "\t");
    System.out.println(rowSet.getString("District"));
   }

  } catch (SQLException e) {
   e.printStackTrace();
  }

 }


 //設定檔
 private static RowSet getConnection1(RowSet rowSet) throws SQLException {
  String serverName = "localhost";
  String database = "world";
  String url = "jdbc:mysql://" + serverName + "/" + database;
  // 帳號和密碼
  String user = "root";
  String password = "root";
  rowSet.setUrl(url);
  rowSet.setUsername(user);
  rowSet.setPassword(password);
  return rowSet;
 }
}

三 prepareStatement可以防止Sql Injection。

資安這條路 04 - [Injection] SQL injection
https://ithelp.ithome.com.tw/articles/10240102

import java.sql.*;

public class jdbc3 {
 public static void main(String[] args) throws Exception {
  try {
   Class.forName("com.mysql.jdbc.Driver");
   System.out.println("Driver loaded!");
  } catch (ClassNotFoundException e) {
   System.out.println("找不到驅動程式類別");
   e.printStackTrace();
  }
  Connection connection = getConnection();
  PreparedStatement statement = connection.prepareStatement("SELECT * FROM world.country WHERE Continent=?");
  statement.setString(1, "Antarctica");
  ResultSet myRs = statement.executeQuery();
  while (myRs.next()) {
   String Name = myRs.getString("Name");
   String Region = myRs.getString("Region");
   System.out.printf("%s, %s\n", Name, Region);
  }

 }


 //設定檔
 private static Connection getConnection() throws SQLException {
  String serverName = "localhost";
  String database = "world";
  String url = "jdbc:mysql://" + serverName + "/" + database;
  // 帳號和密碼
  String user = "root";
  String password = "root";
  return DriverManager.getConnection(url, user, password);
 }

 private static void display(ResultSet myRs) throws SQLException {
  while (myRs.next()) {
   String Name = myRs.getString("Name");
   String Region = myRs.getString("Region");
   System.out.printf("%s, %s\n", Name, Region);
  }
 }

如果要直接用命令提示字元執行程式，要用指令:

1 javac (檔名).java

2 java -cp (class檔案位置路徑，不包含檔名);(mysql-connector-java-5.1.48.jar的所有路徑，包含檔名) (class檔名)

或是先到檔案位置路徑(cd)，在用.代表檔案位置路徑-->java -cp .;(mysql-connector-java-5.1.48.jar的所有路徑，包含檔名) (class檔名)

像是:

java -cp .;D:\Desktop\MySQL\mysql-connector-java-5.1.48\mysql-connector-java-5.1.48.jar jdbc1

就會有這樣的結果:

Driver loaded!
Fri May 22 13:44:19 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Kabul    AFG   Kabol
Qandahar    AFG   Qandahar
Herat    AFG   Herat
Mazar-e-Sharif    AFG   Balkh
Amsterdam    NLD   Noord-Holland
Rotterdam    NLD   Zuid-Holland
Haag    NLD   Zuid-Holland
Utrecht    NLD   Utrecht
Eindhoven    NLD   Noord-Brabant
Tilburg    NLD   Noord-Brabant
Groningen    NLD   Groningen
Breda    NLD   Noord-Brabant

其他問題:

Handling the null value from a resultset in JAVA